package lumis.portal.user;

import lumis.portal.cache.ICacheDataProvider;
import lumis.portal.cache.PortalCache;
import lumis.portal.dao.*;
import lumis.portal.dao.jdbc.ITransactionJdbc;
import lumis.portal.manager.ManagerException;
import lumis.portal.transaction.PortalTransactionFactory;
import lumis.portal.*;
import lumis.util.ITransaction;
import lumis.util.PortalUtil;
import lumis.util.TextUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import lumis.util.JdbcUtil;

/**
 * Implementation of {@link IUserDao} based on JDBC.
 * 
 * @since 4.0.0
 * @version $Revision: 11127 $ $Date: 2009-11-26 11:11:17 -0200 (Thu, 26 Nov 2009) $
 */
public class UserDaoJdbc implements IUserDao
{
	/**
	 * A cache of UserConfig instances.
	 * <p>
	 * Not found entries will be cached as <code>null</code>.
	 * @since 4.1.1
	 */
	private static final PortalCache<UserConfig> userCache = new PortalCache<UserConfig>("lumis.portal.user.UserConfig", new ICacheDataProvider<UserConfig>()
	{
		public UserConfig loadData(String userId) throws PortalException
		{
			try
			{
				ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) PortalTransactionFactory.getCurrentTransaction();
				Connection connection = daoTransactionJdbc.getConnection();
				PreparedStatement statement = connection.prepareStatement("select * from lum_User where userId = ?");
				try
				{
					statement.setString(1, userId);
					ResultSet resultSet = statement.executeQuery();
					try
					{
						if (resultSet.next())
						{
							UserConfig result = new UserConfig();
							result.setId(resultSet.getString("userId"));
							result.setLogin(resultSet.getString("login"));
							result.setFirstName(resultSet.getString("firstName"));
							result.setMiddleName(resultSet.getString("middleName"));
							result.setLastName(resultSet.getString("lastName"));
							result.setEmail(resultSet.getString("email"));
							result.setType(resultSet.getInt("type"));
							result.setDisabled(resultSet.getInt("disabled") == 1);
							result.setMultiSession(resultSet.getInt("multiSession") == 1);
							result.setChannelId(resultSet.getString("channelId"));
							result.setOrigin(resultSet.getString("origin"));
							result.setPortalLogin(resultSet.getInt("portalLogin") > 0);
							return result;
						}
						else
						{
							return null;
						}
					}
					finally
					{
						resultSet.close();
					}
				}
				finally
				{
					statement.close();
				}
			}
			catch (PortalException e)
			{
				throw e;
			}
			catch (Exception e)
			{
				throw new UnexpectedException(e);
			}
		}
	});
	
	public UserConfig get(String userId, ITransaction transaction) throws DaoException, PortalException
	{
		UserConfig result = userCache.fetch(userId);
		if (result != null)
			return result;
		else
			throw new PortalObjectNotFoundException("STR_USER_ID_NOT_FOUND");
	}

	public String getUserIdByLogin(String login, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			String userId = null;
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement("select userId from lum_User where UPPER(login) = UPPER(?)");
			try
			{
				statement.setString(1, login);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					if (resultSet.next())
						userId = resultSet.getString("userId");

				}
				finally
				{
					resultSet.close();
				}
			}
			finally
			{
				statement.close();
			}

			if (userId == null)
			{
				// try to find the userId in the alternative logins table
				PreparedStatement stAlternative = connection.prepareStatement("select userId from lum_UserAlternativeLogin where UPPER(login) = UPPER(?)");
				try
				{
					stAlternative.setString(1, login);
					ResultSet rsAlternative = stAlternative.executeQuery();
					try
					{
						if (rsAlternative.next())
							userId = rsAlternative.getString("userId");

					}
					finally
					{
						rsAlternative.close();
					}
				}
				finally
				{
					stAlternative.close();
				}				
			}

			if (userId == null)
				throw new PortalObjectNotFoundException("STR_USER_LOGIN_NOT_FOUND;" + TextUtil.escapeLocalizationParameter(login));

			return userId;
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	/**
	 * Verifies if the specified login already exists, throwing an exception if
	 * it does.
	 * @param login the login.
	 * @param transaction the transaction for persistence access.
	 * @throws PortalException if the login already exists.
	 * @since 4.0.8
	 */
	private void verifyLoginExists(String login, ITransaction transaction) throws PortalException
	{
		String userId;
		try
		{
			userId = getUserIdByLogin(login, transaction);			
		}
		catch(PortalException e)
		{
			userId = null;
		}
		if (userId != null)
			throw new ManagerException("STR_LOGIN_ALREADY_EXISTS;" + TextUtil.escapeLocalizationParameter(login));		
	}
	
	public void add(UserConfig userConfig, ITransaction transaction) throws DaoException, PortalException
	{
		verifyLoginExists(userConfig.getLogin(), transaction);
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("insert into lum_User (userId, login, password, " + "firstName, middleName, lastName, email, disabled, type, multiSession, channelId, origin, portalLogin) " + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

			statement.setString(1, userConfig.getId());
			statement.setString(2, userConfig.getLogin());
			statement.setString(3, userConfig.getPassword());
			statement.setString(4, userConfig.getFirstName());
			statement.setString(5, userConfig.getMiddleName());
			statement.setString(6, userConfig.getLastName());
			statement.setString(7, userConfig.getEmail());
			statement.setInt(8, userConfig.isDisabled() ? 1 : 0);
			statement.setInt(9, userConfig.getType());
			statement.setInt(10, userConfig.isMultiSession() ? 1 : 0);
			statement.setString(11, userConfig.getChannelId());
			statement.setString(12, userConfig.getOrigin());
			statement.setInt(13, (userConfig.isPortalLogin()? 1 : 0));

			try
			{
				statement.executeUpdate();
			}
			catch (Exception e)
			{
				try
				{
					if (get(userConfig.getId(), daoTransactionJdbc) != null)
						throw new PortalObjectAlreadyExistsException("STR_RESOURCE_ALREADY_EXISTS;" + userConfig.getId(), e);
					else
						throw e;
				}
				catch (PortalObjectNotFoundException e1)
				{
					throw e;
				}
			}
			statement.close();
			
			userCache.remove(userConfig.getId(), transaction);
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	
	public void update(UserConfig userConfig, ITransaction transaction) throws DaoException, PortalException
	{
		UserConfig originalUser = get(userConfig.getId(), transaction);
		if (!originalUser.getLogin().equals(userConfig.getLogin()))
			verifyLoginExists(userConfig.getLogin(), transaction);
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("update lum_User set login=?, firstName=?, middleName=?, lastName=?, email=?, disabled=?, type=?, multiSession=?, channelId = ?, origin = ?, portalLogin = ? where userId = ?");

			statement.setString(1, userConfig.getLogin());
			statement.setString(2, userConfig.getFirstName());
			statement.setString(3, userConfig.getMiddleName());
			statement.setString(4, userConfig.getLastName());
			statement.setString(5, userConfig.getEmail());
			statement.setInt(6, userConfig.isDisabled() ? 1 : 0);
			statement.setInt(7, userConfig.getType());
			statement.setInt(8, userConfig.isMultiSession() ? 1 : 0);
			statement.setString(9, userConfig.getChannelId());
			statement.setString(10, userConfig.getOrigin());			
			statement.setInt(11, userConfig.isPortalLogin() ? 1 : 0);
			statement.setString(12, userConfig.getId());

			statement.executeUpdate();
			statement.close();
			
			userCache.remove(userConfig.getId(), transaction);
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void setPassword(String userId, String password, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("update lum_User set password=? where userId = ?");

			statement.setString(1, password);
			statement.setString(2, userId);

			statement.executeUpdate();
			statement.close();

			userCache.remove(userId, transaction);
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void updateLastLogin(String userId, Date lastLogin) throws DaoException, PortalException
	{
		ITransaction transaction = PortalTransactionFactory.createTransaction();
		try
		{
			transaction.begin();
			
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("update lum_User set lastLogin=? where userId = ?");
			try
			{
				statement.setTimestamp(1, JdbcUtil.getTimestampByDate(lastLogin));
				statement.setString(2, userId);

				statement.executeUpdate();				
			}
			finally
			{
				statement.close();
			}
			
			transaction.commit();
			
			// no need to invalidate cache because last login is not stored in UserConfig
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
		finally
		{
			transaction.dispose();
		}
	}

	public void delete(String userId, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("delete from lum_UserSession where userId = ?");
			statement.setString(1, userId);
			statement.executeUpdate();
			statement.close();

			statement = connection.prepareStatement("delete from lum_User where userId = ?");
			statement.setString(1, userId);
			statement.executeUpdate();
			statement.close();
			
			userCache.remove(userId, transaction);
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public Collection<String> getByChannelId(String channelId, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			Collection<String> users = new ArrayList<String>();

			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement("select userId from lum_User where channelId " + (channelId == null ? " is NULL ":  " = ?"));
			try
			{
				if ( channelId != null )
					statement.setString(1, channelId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					while (resultSet.next())
					{
						users.add(resultSet.getString("userId"));
					}
				}
				finally
				{
					resultSet.close();
				}
			}
			finally
			{
				statement.close();
			}

			return users;
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void addAlternativeLogin(String userId, String newLogin, ITransaction transaction) throws PortalException
	{
		verifyLoginExists(newLogin, transaction);
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("insert into lum_UserAlternativeLogin (id, userId, login) " + "values (?, ?, ?) ");
			try
			{
				statement.setString(1, PortalUtil.generateNewGuid());
				statement.setString(2, userId);
				statement.setString(3, newLogin);
				statement.executeUpdate();
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public Collection<String> getAlternativeLogins(String userId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select login from lum_UserAlternativeLogin where userId = ?");
			try
			{
				statement.setString(1, userId);
				return JdbcUtil.fillListString(statement, "login");
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}		
	}
	
	public void deleteAlternativeLogin(String loginToDelete, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("delete from lum_UserAlternativeLogin where UPPER(login) = UPPER(?)");
			try
			{
				statement.setString(1, loginToDelete);
				statement.executeUpdate();
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public Collection<String> getByOrigin(String origin, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			Collection<String> users = new ArrayList<String>();

			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement("select userId from lum_User where origin  = ?");
			try
			{
				
				statement.setString(1, origin);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					while (resultSet.next())
					{
						users.add(resultSet.getString("userId"));
					}
				}
				finally
				{
					resultSet.close();
				}
			}
			finally
			{
				statement.close();
			}

			return users;
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public Collection<String> getAllUserIds(ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement("select userId from lum_User");
			try
			{
				return JdbcUtil.fillListString(statement, "userId");
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
}